QWI with confidence bands in Excel

In this short presentation,

• we will combine the new rates with the new variability measures to create a graph with error bands in Excel

• Excel
• National QWI http://lehd.census.gov/lehd/data/qwi_national_beta.html or http://go.usa.gov/3EDUG

http://lehd.ces.census.gov/data/qwi/us/R2015Q1/

or

http://go.usa.gov/3EDUz

Use any number of ZIP-compatible products to unzip (here: 7zip). Right-click, extract.

Simply double-click on both files

For simplicity, we move the sheets into the same workbook.

We focus on HirAEndR (and deleted most other columns after it)

On first tab, create columns H_low and H_high

Strict

\[ X_{low} = X_t - t(0.95,df\_X_t) st\_X_t \]

i.e.

h_low = HirAEndR –

t(df_HirAEndR,0.95)*st_HirAEndR

Approximate

\[ X_{low} = X_t - 1.645 st\_X_t \]

i.e.

h_low = HirAEndR – 1.645*st_HirAEndR

You want the yyq, HirAEndR and the high/low columns

We'll be changing it shortly anyway…

Select “Line” for main series, “Area” for bounds (not “Stacked Area”!)

• Delete legend
• H_high should come before H_low in order
  • color light blue
• H_low last in order
  • Color white (not transparent!)
• Adjust size